package cn.edu.scujj.dao;
import cn.edu.scujj.bean.BookType;
import cn.edu.scujj.utils.DButil;
import java.sql.Connection;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookTypeDao {

    public List<BookType> findAll()
        {
            Connection conn = DButil.getConnection();
            String sql = "select * from librarysystem.booktype_info";
            Statement stmt = null;
            List<BookType> list = new ArrayList<>();
            try {
                stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    BookType bookType = new BookType();
                    bookType.setTypeId(rs.getInt("booktype_id"));
                    bookType.setTypeName(rs.getString("booktype_name"));
                    list.add(bookType);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DButil.close(conn);
            }
            return list;
    }
    // 类别添加
    public boolean addBookType(BookType bookType)  {
        Connection conn = DButil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        // 先检查ID是否已存在
        try {
            pstmt = conn.prepareStatement("SELECT 1 FROM librarysystem.booktype_info WHERE booktype_id = ?");
            pstmt.setInt(1, bookType.getTypeId());
            rs = pstmt.executeQuery();//
            if (rs.next()) {
                return false; // ID已存在
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            if (rs != null) try { rs.close(); } catch (SQLException e) {}
            if (pstmt != null) try { pstmt.close(); } catch (SQLException e) {}
        }

        // ID不存在，执行插入
        try {
            pstmt = conn.prepareStatement("INSERT INTO librarysystem.booktype_info (booktype_id,booktype_name) VALUES (?, ?)");
            pstmt.setInt(1, bookType.getTypeId());
            pstmt.setString(2, bookType.getTypeName());
            int result = pstmt.executeUpdate();
            return result > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DButil.close(conn);
        }
    }
    // 类别查询
    public List<BookType> queryBookTypes(String condition, boolean isById, BookType bookType) {
        List<BookType> bookTypes = new ArrayList<>();
        Connection conn = DButil.getConnection();
        String sql = isById ? 
            "SELECT * FROM booktype_info WHERE booktype_id = ?" :
            "SELECT * FROM booktype_info WHERE booktype_name LIKE ?";
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = conn.prepareStatement(sql);
            if (isById) {
                pstmt.setInt(1, Integer.parseInt(condition));
            } else {
                pstmt.setString(1, "%" + condition + "%");
            }

            rs = pstmt.executeQuery();
            while (rs.next()) {
                BookType type = new BookType();
                type.setTypeId(rs.getInt("booktype_id"));
                type.setTypeName(rs.getString("booktype_name"));
                bookTypes.add(type);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) rs.close();
                if (pstmt != null) pstmt.close();
                DButil.close(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return bookTypes;
    }

    // 类别修改
    public boolean updateBookType(BookType bookType) {
        Connection conn = DButil.getConnection();
        PreparedStatement pstmt1 = null;
        PreparedStatement pstmt2 = null;
        try {
            conn.setAutoCommit(false); // 开启事务

            // 1. 更新图书类型表，根据 typeId
            String sql1 = "UPDATE booktype_info SET booktype_name = ? WHERE booktype_id = ?";
            pstmt1 = conn.prepareStatement(sql1);
            pstmt1.setString(1, bookType.getTypeName());
            pstmt1.setInt(2, bookType.getTypeId());
            int result1 = pstmt1.executeUpdate();
            // 2. 更新图书信息表，根据 typeId（假设 book_info 中也有 typeId 字段）
            String sql2 = "UPDATE book_info SET booktype_name = ? WHERE booktype_id = ?";
            pstmt2 = conn.prepareStatement(sql2);
            pstmt2.setString(1, bookType.getTypeName());
            pstmt2.setInt(2, bookType.getTypeId());
            int result2 = pstmt2.executeUpdate();

            conn.commit(); // 提交事务

            if(result1>0&&result2 > 0)
                return true;

        } catch (SQLException e) {
           throw new RuntimeException(e);
            }
        finally {
            DButil.close(conn);
        }
        return false;
    }
    // 类别删除（先检查 book - info 和return-info两个表关联，这里简单查询数量判断，可优化）
    public boolean deleteBookType(BookType bookType)  {
        Connection conn = DButil.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql1 = "SELECT COUNT(*) FROM book_info WHERE booktype_id = ?";
            pstmt = conn.prepareStatement(sql1);
            pstmt.setInt(1, bookType.getTypeId());
            rs = pstmt.executeQuery();
            if (rs.next() && rs.getInt(1) > 0) {
                return false; // 存在书籍关联
            }
// 执行删除
            String deleteSql = "DELETE FROM booktype_info WHERE booktype_id = ?";
            pstmt = conn.prepareStatement(deleteSql);
            pstmt.setInt(1, bookType.getTypeId());
            int result = pstmt.executeUpdate();
            if (result == 0) {
                return false; // 未找到对应类别
            }
        }  catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return true;
    }

    }
